package com.hsnn.medstgmini.sys.dao.impl;

import java.sql.Blob;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.LobHelper;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.project.model.BidProject;
import com.hsnn.medstgmini.sys.dao.SysNoticeDao;
import com.hsnn.medstgmini.sys.model.BidProjNotice;
import com.hsnn.medstgmini.sys.model.BidProjNoticeLog;
import com.hsnn.medstgmini.sys.model.SysNotice;
import com.hsnn.medstgmini.sys.model.SysNoticeLog;
import com.hsnn.medstgmini.util.DateUtil;
import com.hsnn.medstgmini.util.Pagination;

@Repository
public class SysNoticeDaoImpl implements SysNoticeDao{
    
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	
	@Autowired
	private HibernateSqlUtil hibernateUtils;
	/**
	 * @category 获取系统公告列表
	 * @date 2015年8月20日15:44:13
	 * @author zhouqi
	 * @param Pagination
	 * @return Pagination
	 */
	@Override
	public Pagination getSysNoticeList(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append("select A.sys_notice_id as sysNoticId, A.sys_notice_code as sysNoticeCode,");
		sql.append(" A.title as title, A.org as org, A.content as content, C.name as projName, C.proj_id as projId,");
		sql.append(" date_format(A.upd_datetime,'%Y-%m-%d %H:%i:%S') as updDateTime, date_format(A.datetime,'%Y-%m-%d') as dateTime,A.upd_user as updUser,A.url as url,");
		sql.append(" A.read_count as readCount, A.status as status");
		sql.append(" FROM sys_notice A LEFT JOIN bid_projsysnotice B ON A.sys_notice_id = B.sys_notice_id");
		sql.append(" LEFT JOIN bid_project C ON B.proj_id = C.proj_id where 1=1 ");
		Map<String, Object> params = new HashMap<String, Object>();
		if(StringUtils.isNotBlank(page.getConditions().get("title"))){
			sql.append(" and A.title like :title");
			params.put("title", "%"+page.getConditions().get("title")+"%");
		}
		if(StringUtils.isNotBlank(page.getConditions().get("startPoint"))){
			sql.append(" and A.datetime > :startPoint");
			Timestamp startPoint = DateUtil.StringTime2Timestamp(page.getConditions().get("startPoint"));
			params.put("startPoint", startPoint);
		}
		if(StringUtils.isNotBlank(page.getConditions().get("endPoint"))){
			sql.append(" and A.datetime <= :endPoint");
			Timestamp endPoint = DateUtil.StringTime2Timestamp(page.getConditions().get("endPoint"));
			params.put("endPoint", endPoint);
		}
		if(StringUtils.isNotBlank(page.getConditions().get("status"))){
			sql.append(" and A.status = :status");
			params.put("status", page.getConditions().get("status"));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("projId"))){
			sql.append(" and C.proj_id = :projId");
			params.put("projId", page.getConditions().get("projId"));
		}
		sql.append(" order by a.upd_datetime desc ");
		return hibernateUtils.getSqlPageMapList(sql.toString(), params, page);
	}
    
	/**
	 * @category 根据公告id查询公告
	 * @date 2015年8月20日15:44:13
	 * @author zhouqi
	 * @param String id
	 * @return SysNotice
	 */
	@Override
	public SysNotice getSysNoticeById(String sysNoticeId) {
		Session session = sessionFactory.getCurrentSession();
		SysNotice sysNotice = (SysNotice) session.get(SysNotice.class, sysNoticeId);
		session.flush();
		session.clear();
		return sysNotice;
	}
	
	/**
	 * @category 根据公告id查询公告
	 * @date 2015年8月20日15:44:13
	 * @author zhouqi
	 * @param String id
	 * @return SysNotice
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<SysNotice> getSysNoticeByTitle(String sysNoticeTitle) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from sys_notice where sys_notice.title = :sysNoticeTitle");
		Query query = session.createSQLQuery(sql.toString()).addEntity(SysNotice.class);
	    query.setParameter("sysNoticeTitle", sysNoticeTitle);
		List<SysNotice> list = query.list();     
		return list;
	}
    
	/**
	 * @category 根据公告id查询公告日志
	 * @date 2015年8月20日15:44:13
	 * @author zhouqi
	 * @param String id
	 * @return SysNoticeLog
	 */
	@Override
	public SysNoticeLog getSysNoticeLogById(String sysNoticeLogId) {
		Session session = sessionFactory.getCurrentSession();
		SysNoticeLog sysNoticeLog = (SysNoticeLog) session.get(SysNoticeLog.class, sysNoticeLogId);
		return sysNoticeLog;
	}
	
	/**
	 * @category 保存公告
	 * @date 2015年8月21日08:31:45
	 * @author zhouqi
	 * @param SysNotice sysNotice
	 * @return SysNoticeLog
	 */
	@Override
	public void saveSysNotice(SysNotice sysNotice) {
		Session session = sessionFactory.getCurrentSession();
		LobHelper lobHelper = session.getLobHelper();
		try{
			if(StringUtils.isNoneBlank(sysNotice.getContent())){
				Blob blob;
				blob = lobHelper.createBlob(sysNotice.getContent().getBytes("utf-8"));
				sysNotice.setBlobContent(blob);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		session.save(sysNotice);
		session.flush();
	}

	/**
	 * @category 保存公告日志
	 * @date 2015年8月21日08:31:45
	 * @author zhouqi
	 * @param SysNoticeLog sysNoticLog
	 * @return SysNoticeLog
	 */
	@Override
	public void saveSysNoticeLog(SysNoticeLog sysNoticLog) {
		Session session = sessionFactory.getCurrentSession();
		LobHelper lobHelper = session.getLobHelper();
		if(StringUtils.isNoneBlank(sysNoticLog.getContent())){
			Blob blob = lobHelper.createBlob(sysNoticLog.getContent().getBytes());
			sysNoticLog.setBlobContent(blob);
		}
		session.save(sysNoticLog);
		session.flush();
	}
	
	/**
	 * @category 更新公告
	 * @date 2015年8月21日08:31:45
	 * @author zhouqi
	 * @param SysNotice sysNotice
	 * @return SysNoticeLog
	 */
	@Override
	public void updateSysNotice(SysNotice sysNotice) {
		Session session = sessionFactory.getCurrentSession();
		LobHelper lobHelper = session.getLobHelper();
		if(StringUtils.isNotBlank(sysNotice.getContent())){
			Blob blob = lobHelper.createBlob(sysNotice.getContent().getBytes());
			sysNotice.setBlobContent(blob);
		}
		session.update(sysNotice);
		session.flush();
	}

	/**
	 * @category 更新公告日志
	 * @date 2015年8月21日08:31:45
	 * @author zhouqi
	 * @param SysNoticeLog sysNoticLog
	 * @return SysNoticeLog
	 */
	@Override
	public void updateSysNoticeLog(SysNoticeLog sysNoticLog) {
		Session session = sessionFactory.getCurrentSession();
		session.save(sysNoticLog);
		session.flush();
	}
	
	/**
	 * @category 保存公告日志列表
	 * @date 2015年8月21日08:31:45
	 * @author zhouqi
	 * @param SysNoticeLog sysNoticLog
	 * @return SysNoticeLog
	 */
	@Override
	public Pagination getSysNoticeLogList(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append("select A.log_id as logId,A.read_count as readCount, A.sys_notice_id as sysNoticeId, A.sys_notice_code as sysNoticeCode,");
		sql.append(" A.title as title, A.org as org, A.content as content,");
		sql.append(" date_format(A.upd_datetime,'%Y-%m-%d %H:%i:%S') as updDateTime, date_format(A.datetime,'%Y-%m-%d %H:%i:%S') as dateTime,A.upd_user as updUser");
		sql.append(" ,A.status as status");
		sql.append(" from sys_notice_log A where 1 =1");
		Map<String, Object> params = new HashMap<String, Object>();
		if(StringUtils.isNotBlank(page.getConditions().get("title"))){
			sql.append(" and A.title like :title");//根据标题查询
			params.put("title", page.getConditions().get("title"));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("startPoint"))){
			sql.append(" and A.upd_datetime > :startPoint");//根据更改时间查询
			Timestamp startPoint = DateUtil.StringTime2Timestamp(page.getConditions().get("startPoint"));
			params.put("startPoint", startPoint);
		}
		if(StringUtils.isNotBlank(page.getConditions().get("endPoint"))){
			sql.append(" and A.upd_datetime <= :endPoint");
			Timestamp endPoint = DateUtil.StringTime2Timestamp(page.getConditions().get("endPoint"));
			params.put("endPoint", endPoint);
		}
		if(StringUtils.isNotBlank(page.getConditions().get("status"))){
			sql.append(" and A.status = :status");
			params.put("status", page.getConditions().get("status"));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("sysNoticeId"))){
			sql.append(" and A.sys_notice_id = :sysNoticeId");
			params.put("sysNoticeId", page.getConditions().get("sysNoticeId"));
		}
		sql.append(" order by A.upd_datetime DESC");
		return hibernateUtils.getSqlPageMapList(sql.toString(), params, page);
	}
	/**
	 * @category 保存公告日志列表
	 * @date 2015年8月21日08:31:45
	 * @author zhouqi
	 * @param SysNoticeLog sysNoticLog
	 * @return SysNoticeLog
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<SysNoticeLog> getCompareLog(String sysNoticeId,Timestamp updDateTime){
		Session session = sessionFactory.getCurrentSession();
	    StringBuffer sql = new StringBuffer();
	    sql.append("select * from sys_notice_log A where A.sys_notice_id = :sysNoticeId and A.upd_datetime <= :updDateTime order by A.upd_datetime DESC limit 0,2");
	    Query query = session.createSQLQuery(sql.toString()).addEntity(SysNoticeLog.class);
	    query.setParameter("sysNoticeId", sysNoticeId);
	    query.setParameter("updDateTime", updDateTime);
	    List<SysNoticeLog> list =  query.list();
	    return list;
	}
	
	/**
	 * @category 获取项目列表（查询用）
	 * @date 2015年8月21日08:31:45
	 * @author zhouqi
	 * @param SysNoticeLog sysNoticLog
	 * @return SysNoticeLog
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<BidProject> getProjectList(){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from bid_project");
		Query query = session.createSQLQuery(sql.toString()).addEntity(BidProject.class);
		return query.list();
	}
	
	/**
	 * @category 保存或者更新项目公告关系
	 * @date 2015年8月21日08:31:45
	 * @author zhouqi
	 * @param SysNoticeLog sysNoticLog
	 * @return SysNoticeLog
	 */
	@Override
	public void saveOrUpdBidProjNotice(BidProjNotice bidProjNotice){
			Session session = sessionFactory.getCurrentSession();
			session.saveOrUpdate(bidProjNotice);	
			session.flush();
			session.clear();
	}
	
	/**
	 * @category 保存或者更新项目公告关系日志
	 * @date 2015年8月21日08:31:45
	 * @author zhouqi
	 * @param SysNoticeLog sysNoticLog
	 * @return SysNoticeLog
	 */
	@Override
	public void saveBidProjNoticeLog(BidProjNoticeLog bidProjNoticeLog){
			Session session = sessionFactory.getCurrentSession();
			session.saveOrUpdate(bidProjNoticeLog);	
			session.flush();
			session.clear();
	}
	
	/**
	 * @category 根据公告id获取项目公告关系
	 * @date 2015年8月21日08:31:45
	 * @author zhouqi
	 * @param SysNoticeLog sysNoticLog
	 * @return SysNoticeLog
	 */
	@SuppressWarnings("unchecked")
	@Override
	public BidProjNotice getBidProjNoticeById(String sysNoticeId){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from bid_projsysnotice where sys_notice_id = :sysNoticeId ");
		Query query = session.createSQLQuery(sql.toString()).addEntity(BidProjNotice.class);
		query.setParameter("sysNoticeId", sysNoticeId);
		List<BidProjNotice> list = query.list();
		if(list.size() >=1){
			return list.get(0);
		}
		return null;
	}

	/**
	 * @category 获取首页系统公告列表
	 * @author 韩守松
	 * @date   2015年11月16日
	 * @param  @param page
	 * @param  @return
	 */
    @SuppressWarnings("unchecked")
	public List<SysNotice> getHomeSysNoticeList(Pagination page){
    	Session session = sessionFactory.getCurrentSession();
    	StringBuffer sql = new StringBuffer();
		sql.append("select A.sys_notice_id as sysNoticId, A.sys_notice_code as sysNoticeCode,");
		sql.append(" A.title as title, A.org as org, A.content as content, C.name as projName, C.proj_id as projId,");
		sql.append(" date_format(A.upd_datetime,'%Y-%m-%d %H:%i:%S') as updDateTime, date_format(A.datetime,'%Y-%m-%d') as dateTime,A.upd_user as updUser,A.url as url,");
		sql.append(" A.read_count as readCount, A.status as status");
		sql.append(" FROM sys_notice A LEFT JOIN bid_projsysnotice B ON A.sys_notice_id = B.sys_notice_id");
		sql.append(" LEFT JOIN bid_project C ON B.proj_id = C.proj_id where 1=1 and A.status = 1 ");
		sql.append(" order by a.upd_datetime desc limit 10 ");
		SQLQuery query = session.createSQLQuery(sql.toString());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
    }
}
